﻿using Global.Globalcs;
using System;
using System.Data;

namespace PHZPNW.API
{
    public class YGDA
    {
        MSql msql = new MSql();
        public YGDA()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
        //加载员工表
        public string Getygb(string[] cbxx)
        {
            string sql = "";
            string tj = "";
            string sjd = "";
            string ssbm = "";
            if(cbxx[0] != "")
            {
                tj = " and "+ cbxx[0] + " like '%"+ cbxx[1] + "%'";
            }
            if(cbxx[4]==""&& cbxx[5] == "")
            {
                sjd = "";
            }
            else if(cbxx[4] != "" && cbxx[5] == "")
            {
                sjd = " and rzsj >= '"+cbxx[4]+"' ";
            }
            else if(cbxx[4] == "" && cbxx[5] != "")
            {
                sjd = " and rzsj <='"+cbxx[5]+"'";
            }
            else
            {
                sjd = " and rzsj between '"+cbxx[4]+"' and '"+cbxx[5]+"'";
            }
            if(cbxx[3]!="")
            {
                ssbm = " and ygbm = '"+ cbxx[3] + "'";
            }
            string jdate = "";
            sql = "select * from V_workinfo where  zzzt like '%"+ cbxx[2] + "%' "+ ssbm + " "+tj+" "+sjd+"";
            DataSet ds = msql.Selbeck(sql);
            jdate = "[";
            for (int i=0;i<ds.Tables[0].Rows.Count;i++)
            {
                string rzsj = Convert.ToDateTime(ds.Tables[0].Rows[i]["rzsj"].ToString()).ToShortDateString();
                if (i==0)
                {
                    jdate = jdate + "{\"workid\":\"" + ds.Tables[0].Rows[i]["workid"].ToString() + "\",\"workname\":\"" + ds.Tables[0].Rows[i]["workname"].ToString() + "\"" +
                        ",\"ygbm\":\"" + ds.Tables[0].Rows[i]["bmname"].ToString() + "\",\"yggw\":\"" + ds.Tables[0].Rows[i]["gwmc"].ToString() + "\"" +
                        ",\"rzrq\":\"" + rzsj + "\",\"sfz\":\"" + ds.Tables[0].Rows[i]["sfz"].ToString() + "\"" +
                        ",\"telephone\":\"" + ds.Tables[0].Rows[i]["telephone"].ToString() + "\",\"xl\":\"" + ds.Tables[0].Rows[i]["xlm"].ToString() + "\"" +
                        ",\"hjszd\":\"" + ds.Tables[0].Rows[i]["hjszd"].ToString() + "\"}";
                }
                else
                {
                    jdate = jdate + ",{\"workid\":\"" + ds.Tables[0].Rows[i]["workid"].ToString() + "\",\"workname\":\"" + ds.Tables[0].Rows[i]["workname"].ToString() + "\"" +
                        ",\"ygbm\":\"" + ds.Tables[0].Rows[i]["bmname"].ToString() + "\",\"yggw\":\"" + ds.Tables[0].Rows[i]["gwmc"].ToString() + "\"" +
                        ",\"rzrq\":\"" + rzsj + "\",\"sfz\":\"" + ds.Tables[0].Rows[i]["sfz"].ToString() + "\"" +
                        ",\"telephone\":\"" + ds.Tables[0].Rows[i]["telephone"].ToString() + "\",\"xl\":\"" + ds.Tables[0].Rows[i]["xlm"].ToString() + "\"" +
                        ",\"hjszd\":\"" + ds.Tables[0].Rows[i]["hjszd"].ToString() + "\"}";
                }
            }
            jdate = jdate + "]";
            return jdate;
        }
        //加载员工基础信息
        public string[] Getyginfo(string workid)
        {
            string[] jdate=new string[32];
            //基本信息赋值
            string sql = "select * from F_YGJC where workid='"+ workid + "'";
            DataSet ds = msql.Selbeck(sql);
            if(ds.Tables[0].Rows.Count>0)
            {
                jdate[0] = ds.Tables[0].Rows[0]["workid"].ToString();
                jdate[1] = ds.Tables[0].Rows[0]["workname"].ToString();
                jdate[2] = ds.Tables[0].Rows[0]["birthday"].ToString();
                jdate[3] = ds.Tables[0].Rows[0]["sex"].ToString();
                jdate[4] = ds.Tables[0].Rows[0]["mz"].ToString();
                jdate[5] = ds.Tables[0].Rows[0]["url"].ToString();
                jdate[6] = ds.Tables[0].Rows[0]["zzmm"].ToString();
                jdate[7] = ds.Tables[0].Rows[0]["xl"].ToString();
                jdate[8] = ds.Tables[0].Rows[0]["jsj"].ToString();
                jdate[9] = ds.Tables[0].Rows[0]["dzsd"].ToString();
                jdate[10] = ds.Tables[0].Rows[0]["sg"].ToString();
                jdate[11] = ds.Tables[0].Rows[0]["tz"].ToString();
                jdate[12] = ds.Tables[0].Rows[0]["qq"].ToString();
                jdate[13] = ds.Tables[0].Rows[0]["mail"].ToString();
                jdate[14] = ds.Tables[0].Rows[0]["telephone"].ToString();
                jdate[15] = ds.Tables[0].Rows[0]["phone"].ToString();
                jdate[16] = ds.Tables[0].Rows[0]["xzdz"].ToString();
                jdate[17] = ds.Tables[0].Rows[0]["sfz"].ToString();
                jdate[18] = ds.Tables[0].Rows[0]["zzz"].ToString();
                jdate[19] = ds.Tables[0].Rows[0]["hjxz"].ToString();
                jdate[20] = ds.Tables[0].Rows[0]["hjszd"].ToString();
                jdate[21] = ds.Tables[0].Rows[0]["jzlx"].ToString();
                jdate[22] = ds.Tables[0].Rows[0]["hqsj"].ToString();
                jdate[23] = ds.Tables[0].Rows[0]["yxjs"].ToString();
                jdate[24] = ds.Tables[0].Rows[0]["xzczz"].ToString();
                jdate[25] = ds.Tables[0].Rows[0]["jjlxr"].ToString();
                jdate[26] = ds.Tables[0].Rows[0]["jjlxdh"].ToString();
                jdate[27] = ds.Tables[0].Rows[0]["jjlxdz"].ToString();
                jdate[28] = ds.Tables[0].Rows[0]["yhk"].ToString();
                jdate[29] = ds.Tables[0].Rows[0]["ygbm"].ToString();
                jdate[30] = ds.Tables[0].Rows[0]["yggw"].ToString();
                jdate[31] = ds.Tables[0].Rows[0]["rzsj"].ToString();
            }
            else
            {
                for(int i=0;i<32;i++)
                {
                    jdate[i] = "";
                }
            }
            return jdate;
        }
        //加载员工家庭信息
        public string[] Getygjtinfo(string workid)
        {
            string[] jdate = new string[19];
            string sql = "select * from F_YGJT where workid='" + workid + "'";
            DataSet ds = msql.Selbeck(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                jdate[0] = ds.Tables[0].Rows[0]["hp"].ToString();
                jdate[1] = ds.Tables[0].Rows[0]["poxm"].ToString();
                jdate[2] = ds.Tables[0].Rows[0]["podw"].ToString();
                jdate[3] = ds.Tables[0].Rows[0]["hzxm1"].ToString();
                jdate[4] = ds.Tables[0].Rows[0]["hznl1"].ToString();
                jdate[5] = ds.Tables[0].Rows[0]["hzxb1"].ToString();
                jdate[6] = ds.Tables[0].Rows[0]["hzdw1"].ToString();
                jdate[7] = ds.Tables[0].Rows[0]["hzxm2"].ToString();
                jdate[8] = ds.Tables[0].Rows[0]["hznl2"].ToString();
                jdate[9] = ds.Tables[0].Rows[0]["hzxb2"].ToString();
                jdate[10] = ds.Tables[0].Rows[0]["hzdw2"].ToString();
                jdate[11] = ds.Tables[0].Rows[0]["fqxm"].ToString();
                jdate[12] = ds.Tables[0].Rows[0]["fqdw"].ToString();
                jdate[13] = ds.Tables[0].Rows[0]["mqxm"].ToString();
                jdate[14] = ds.Tables[0].Rows[0]["mqdw"].ToString();
                jdate[15] = ds.Tables[0].Rows[0]["xmxm"].ToString();
                jdate[16] = ds.Tables[0].Rows[0]["xmdw"].ToString();
                jdate[17] = ds.Tables[0].Rows[0]["poxdjm"].ToString();
                jdate[18] = ds.Tables[0].Rows[0]["poxdjmdw"].ToString();
            }
            else
            {
                for (int i = 0; i < 19; i++)
                {
                    jdate[i] = "";
                }
            }
            return jdate;
        }
        //加载员工教育培训信息
        public string[] Getygjyinfo(string workid)
        {
            string[] jdate;
            string sql = "select * from F_YGJY where workid='" + workid + "' order by id";
            DataSet ds = msql.Selbeck(sql);
            jdate = new string[6 * ds.Tables[0].Rows.Count + 1];
            jdate[0]= ds.Tables[0].Rows.Count.ToString();
            for (int i=0;i<ds.Tables[0].Rows.Count;i++)
            {
                jdate[6 * i + 1] = ds.Tables[0].Rows[i]["kssj"].ToString();
                jdate[6 * i + 2] = ds.Tables[0].Rows[i]["jssj"].ToString();
                jdate[6 * i + 3] = ds.Tables[0].Rows[i]["xxmc"].ToString();
                jdate[6 * i + 4] = ds.Tables[0].Rows[i]["xl"].ToString();
                jdate[6 * i + 5] = ds.Tables[0].Rows[i]["zy"].ToString();
                jdate[6 * i + 6] = ds.Tables[0].Rows[i]["zs"].ToString();
            }
            return jdate;
        }
        //加载员工工作经历信息
        public string[] Getyggzinfo(string workid)
        {
            string[] jdate;
            string sql = "select * from F_YGGZ where workid='" + workid + "'";
            DataSet ds = msql.Selbeck(sql);
            jdate = new string[6 * ds.Tables[0].Rows.Count + 1];
            jdate[0] = ds.Tables[0].Rows.Count.ToString();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                jdate[6 * i + 1] = ds.Tables[0].Rows[i]["kssj"].ToString();
                jdate[6 * i + 2] = ds.Tables[0].Rows[i]["jssj"].ToString();
                jdate[6 * i + 3] = ds.Tables[0].Rows[i]["gzdw"].ToString();
                jdate[6 * i + 4] = ds.Tables[0].Rows[i]["zwbm"].ToString();
                jdate[6 * i + 5] = ds.Tables[0].Rows[i]["lzyy"].ToString();
                jdate[6 * i + 6] = ds.Tables[0].Rows[i]["yx"].ToString();
            }
            return jdate;
        }
        //加载员工其他信息
        public string[] Getygqtinfo(string workid)
        {
            string[] jdate = new string[13];
            string sql = "select * from F_YGQT where workid='" + workid + "'";
            DataSet ds = msql.Selbeck(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                jdate[0] = ds.Tables[0].Rows[0]["dyyz"].ToString();
                jdate[1] = ds.Tables[0].Rows[0]["dysp"].ToString();
                jdate[2] = ds.Tables[0].Rows[0]["deyz"].ToString();
                jdate[3] = ds.Tables[0].Rows[0]["desp"].ToString();
                jdate[4] = ds.Tables[0].Rows[0]["hy"].ToString();
                jdate[5] = ds.Tables[0].Rows[0]["xzb"].ToString();
                jdate[6] = ds.Tables[0].Rows[0]["crb"].ToString();
                jdate[7] = ds.Tables[0].Rows[0]["cj"].ToString();
                jdate[8] = ds.Tables[0].Rows[0]["dx"].ToString();
                jdate[9] = ds.Tables[0].Rows[0]["gy"].ToString();
                jdate[10] = ds.Tables[0].Rows[0]["az"].ToString();
                jdate[11] = ds.Tables[0].Rows[0]["ywbs"].ToString();
                jdate[12] = ds.Tables[0].Rows[0]["xqah"].ToString();
            }
            else
            {
                for (int i = 0; i < 13; i++)
                {
                    jdate[i] = "";
                }
            }
            return jdate;
        }
        //处理员工基本信息
        public string YGJBXX(string[] cbxx)
        {
            if (!GHCheck(cbxx[0], cbxx[32]))
            {
                return "员工工号重复";
            }
            string sql = "";
            string url = cbxx[5].Substring(cbxx[5].IndexOf("img/"));
            if (cbxx[32] == "0")
            {
                string id = msql.getmaxid("id", "F_YGJC", "");
                
                sql = "insert into F_YGJC (id,workid,workname,birthday,sex,mz,url,zzmm,xl,jsj,dzsd,sg,tz,qq,mail,telephone,phone,xzdz,sfz,zzz,hjxz,hjszd,jzlx,hqsj,yxjs,xzczz,jjlxr,jjlxdh,jjlxdz,yhk," +
                    "ygbm,yggw,rzsj,zzzt)Values('" + id + "','" + cbxx[0] + "','" + cbxx[1] + "','" + cbxx[2] + "','" + cbxx[3] + "','" + cbxx[4] + "','" + url + "','" + cbxx[6] + "','" + cbxx[7] + "'," +
                    "'" + cbxx[8] + "','" + cbxx[9] + "','" + cbxx[10] + "','" + cbxx[11] + "','" + cbxx[12] + "','" + cbxx[13] + "','" + cbxx[14] + "','" + cbxx[15] + "','" + cbxx[16] + "','" + cbxx[17] + "'" +
                    ",'" + cbxx[18] + "','" + cbxx[19] + "','" + cbxx[20] + "','" + cbxx[21] + "','" + cbxx[22] + "','" + cbxx[23] + "','" + cbxx[24] + "','" + cbxx[25] + "','" + cbxx[26] + "'" +
                    ",'" + cbxx[27] + "','" + cbxx[28] + "','" + cbxx[29] + "','" + cbxx[30] + "','" + cbxx[31] + "',0)";
            }
            else
            {
                sql = "update F_YGJC set workname='" + cbxx[1] + "',birthday='" + cbxx[2] + "',sex='" + cbxx[3] + "',mz='" + cbxx[4] + "',url='" + url + "',zzmm='" + cbxx[6] + "'," +
                    "xl='" + cbxx[7] + "',jsj='" + cbxx[8] + "',dzsd='" + cbxx[9] + "',sg='" + cbxx[10] + "',tz='" + cbxx[11] + "',qq='" + cbxx[12] + "',mail='" + cbxx[13] + "'," +
                    "telephone='" + cbxx[14] + "',phone='" + cbxx[15] + "',xzdz='" + cbxx[16] + "',sfz='" + cbxx[17] + "',zzz='" + cbxx[18] + "',hjxz='" + cbxx[19] + "',hjszd='" + cbxx[20] + "'," +
                    "jzlx='" + cbxx[21] + "',hqsj='" + cbxx[22] + "',yxjs='" + cbxx[23] + "',xzczz='" + cbxx[24] + "',jjlxr='" + cbxx[25] + "',jjlxdh='" + cbxx[26] + "',jjlxdz='" + cbxx[27] + "'," +
                    "yhk='" + cbxx[28] + "',ygbm='" + cbxx[29] + "',yggw='" + cbxx[30] + "',rzsj='" + cbxx[31] + "' where  workid = '"+ cbxx[0] + "'";
            }

            if (msql.Datachange(sql))
            {
                return "";
            }
            else
            {
                return "基础信息写入异常";
            }
        }
        //员工家庭信息
        public string YGJTXX(string[] cbxx)
        {
            string sql = "";
            if (cbxx[20] == "0")
            {
                string id = msql.getmaxid("id", "F_YGJT", "");
                sql = "insert into F_YGJT (id,workid,hp,poxm,podw,hzxm1,hznl1,hzxb1,hzdw1,hzxm2,hznl2,hzxb2,hzdw2,fqxm,fqdw,mqxm,mqdw,xmxm,xmdw,poxdjm,poxdjmdw)Values" +
                    "('" + id + "','" + cbxx[19] + "','" + cbxx[0] + "','" + cbxx[1] + "','" + cbxx[2] + "','" + cbxx[3] + "','" + cbxx[4] + "','" + cbxx[5] + "','" + cbxx[6] + "','" + cbxx[7] + "'" +
                    ",'" + cbxx[8] + "','" + cbxx[9] + "','" + cbxx[10] + "','" + cbxx[11] + "','" + cbxx[12] + "','" + cbxx[13] + "','" + cbxx[14] + "','" + cbxx[15] + "','" + cbxx[16] + "'" +
                    ",'" + cbxx[17] + "','" + cbxx[18] + "')";
            }
            else
            {
                sql = "update F_YGJT set hp='" + cbxx[0] + "',poxm='" + cbxx[1] + "',podw='" + cbxx[2] + "',hzxm1='" + cbxx[3] + "',hznl1='" + cbxx[4] + "',hzxb1='" + cbxx[5] + "'," +
                   "hzdw1='" + cbxx[6] + "',hzxm2='" + cbxx[7] + "',hznl2='" + cbxx[8] + "',hzxb2='" + cbxx[9] + "',hzdw2='" + cbxx[10] + "',fqxm='" + cbxx[11] + "',fqdw='" + cbxx[12] + "'," +
                   "mqxm='" + cbxx[13] + "',mqdw='" + cbxx[14] + "',xmxm='" + cbxx[15] + "',xmdw='" + cbxx[16] + "',poxdjm='" + cbxx[17] + "',poxdjmdw='" + cbxx[18] + "' where workid = '" + cbxx[19] + "'";
            }
            if (msql.Datachange(sql))
            {
                return "";
            }
            else
            {
                return "家庭信息写入异常";
            }
        }
        //员工教育培训信息
        public string YGJYXX(string[] cbxx)
        {
            string sql = "";
            int zts = cbxx.Length;
            //确定一共有多少条教育信息
            int xxts = (cbxx.Length - 1) / 6;
            string workid = cbxx[zts - 2];
            if (cbxx[zts - 1] == "0")
            {
                for(int i = 0; i < xxts; i++)
                {
                    string id = msql.getmaxid("id", "F_YGJY", "");
                    sql = "insert into F_YGJY (id,workid,kssj,jssj,xxmc,xl,zy,zs)Values('" + id + "','" + workid + "','" + cbxx[6 * i] + "','" + cbxx[6 * i + 1] + "'," +
                        "'" + cbxx[6 * i + 2] + "','" + cbxx[6 * i + 3] + "','" + cbxx[6 * i + 4] + "','" + cbxx[6 * i + 5] + "')";
                    if (msql.Datachange(sql))
                    {
                        
                    }
                    else
                    {
                        return "培训/教育信息写入异常";
                    }
                }
            }
            else
            {
               
                    for (int i = 0; i < xxts; i++)
                    {
                        sql = "update F_YGJY set kssj='" + cbxx[6 * i] + "',jssj='" + cbxx[6 * i+1] + "',xxmc='" + cbxx[6 * i+2] + "',xl='" + cbxx[6 * i+3] + "',zy='" + cbxx[6 * i+4] + "'," +
                            "zs='" + cbxx[6 * i+5] + "' where  workid = '" + workid + "'";
                        if (msql.Datachange(sql))
                        {
                            
                        }
                        else
                        {
                            return "培训/教育信息更新异常";
                        }
                    }
                
            }
            return "";
        }
        //员工工作经验信息
        public string YGGZXX(string[] cbxx)
        {
            string sql = "";
            int zts = cbxx.Length;
            int xxts = (cbxx.Length - 1) / 6;
            string workid = cbxx[zts - 2];
            if (cbxx[zts - 1] == "0")
            {
                for (int i = 0; i < xxts; i++)
                {
                    string id = msql.getmaxid("id", "F_YGGZ", "");
                    sql = "insert into F_YGGZ (id,workid,kssj,jssj,gzdw,zwbm,lzyy,yx)Values('" + id + "','" + workid + "','" + cbxx[6 * i] + "','" + cbxx[6 * i + 1] + "'," +
                        "'" + cbxx[6 * i + 2] + "','" + cbxx[6 * i + 3] + "','" + cbxx[6 * i + 4] + "','" + cbxx[6 * i + 5] + "')";
                    if (msql.Datachange(sql))
                    {

                    }
                    else
                    {
                        return "员工工作经历信息写入异常";
                    }
                }
            }
            else
            {
                for (int i = 0; i < xxts; i++)
                {
                    sql = "update F_YGGZ set kssj='" + cbxx[6 * i] + "',jssj='" + cbxx[6 * i + 1] + "',gzdw='" + cbxx[6 * i + 2] + "',zwbm='" + cbxx[6 * i + 3] + "',lzyy='" + cbxx[6 * i + 4] + "'," +
                        "yx='" + cbxx[6 * i + 5] + "' where  workid = '" + workid + "'";
                    if (msql.Datachange(sql))
                    {

                    }
                    else
                    {
                        return "培训/教育信息更新异常";
                    }
                }
            }
            return "";
        }
        //员工其他信息
        public string YGQTXX(string[] cbxx)
        {
            string sql = "";
            if (cbxx[14] == "0")
            {
                string id = msql.getmaxid("id", "F_YGQT", "");
                sql = "insert into F_YGQT (id,workid,dyyz,dysp,deyz,desp,hy,xzb,crb,cj,dx,gy,az,ywbs,xqah)Values" +
                    "('" + id + "','" + cbxx[13] + "','" + cbxx[0] + "','" + cbxx[1] + "','" + cbxx[2] + "','" + cbxx[3] + "','" + cbxx[4] + "','" + cbxx[5] + "','" + cbxx[6] + "','" + cbxx[7] + "'" +
                    ",'" + cbxx[8] + "','" + cbxx[9] + "','" + cbxx[10] + "','" + cbxx[11] + "','" + cbxx[12] + "')";
            }
            else
            {
                sql = "update F_YGQT set dyyz='" + cbxx[0] + "',dysp='" + cbxx[1] + "',deyz='" + cbxx[2] + "',desp='" + cbxx[3] + "',hy='" + cbxx[4] + "',xzb='" + cbxx[5] + "'," +
                  "crb='" + cbxx[6] + "',cj='" + cbxx[7] + "',dx='" + cbxx[8] + "',gy='" + cbxx[9] + "',az='" + cbxx[10] + "',ywbs='" + cbxx[11] + "',xqah='" + cbxx[12] + "'" +
                  " where  workid = '" + cbxx[13] + "'";
            }

            if (msql.Datachange(sql))
            {
                return "";
            }
            else
            {
                return "其他信息写入异常";
            }
        }
        //数据插入前先确定工号是否重复
        protected bool GHCheck(string workid, string uori)
        {
            if (uori == "0")
            {
                string sql = "select * from F_YGJC where workid = '" + workid + "'";
                if (msql.Selcount(sql) > 0)
                {
                    return false;
                }
            }
            return true;
        }
        //动态获取岗位下拉菜单表
        public string[] Getgwlb(string bmid)
        {
            string sql = "select * from F_DGDB where bmid = '"+ bmid + "'";
            DataSet ds = msql.Selbeck(sql);
            string[] gwb = new string [2*ds.Tables[0].Rows.Count] ;
            for(int i=0;i<ds.Tables[0].Rows.Count;i++)
            {
                gwb[2*i] = ds.Tables[0].Rows[i]["gwid"].ToString();
                gwb[2*i+1] = ds.Tables[0].Rows[i]["gwmc"].ToString();
            }
            return gwb;
        }
    }
}